﻿using Apps.IDAL;
using Dapper;
using Heima.OA.IBLL;
using Heima.OA.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Heima.OA.BLL
{
    public partial class ZS_CheckMemberService : BaseService<ZS_CheckMember>, IZS_CheckMemberService
    {
        /// <summary>
        /// 多表关联的分页查询
        /// </summary>
        /// <param name="pageSize"></param>
        /// <param name="pageIndex"></param>
        /// <param name="total"></param>
        /// <param name="where"></param>
        /// <param name="order"></param>
        /// <returns></returns>
        public IEnumerable<CheckMemberView> GetPageEntities(int pageSize, int pageIndex, out int total, string where, string order)
        {
            total = 0;
            string oldwhere = where;
            where = !string.IsNullOrEmpty(where) ? " where " + where : "";
            string sql = string.Format(@"with oa as (
	                    select ROW_NUMBER() over (order by Id) as num, * 
	                    from ZS_CheckMember {0}
                    ),
                    ob as (
	                    select * from oa
	                    where num between  {1} and {2}
                    )

                    select oc.FamilyCode, ob.*
                     from ob 
                     left join ZS_CheckFamilyInfo as oc
                     on oc.Id=ob.CheckFamilyId ", where, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
            IEnumerable<CheckMemberView> viewList = DapperHelper.GetAppMall.Query<CheckMemberView>(sql);
            total = GetCount(oldwhere);
            return viewList;
        }
        public int GetCount(string where)
        {
            where = !string.IsNullOrEmpty(where) ? " where " + where : "";
            string sql = string.Format(@"
                    select COUNT(1) from ZS_CheckMember
                     {0} ", where);
            int count = (int)DapperHelper.GetAppMall.ExecuteScalar(sql);
            return count;
        }
       

    }
}
